﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace WCSY.DAL
{
    public class wcsy_costsOtherOrderMstService
    {
        public wcsy_costsOtherOrderMstService()
		{}
		#region  Method


		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(string sysNo)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from wcsy_costsOtherOrderMst");
			strSql.Append(" where sysNo='"+sysNo+"' ");
			return DBHelper.Exists(strSql.ToString());
		}

		/// <summary>
		/// 增加一条数据
		/// </summary>
		public string Add(WCSY.MODEL.wcsy_costsOtherOrderMst model)
		{
			StringBuilder strSql=new StringBuilder();
			StringBuilder strSql1=new StringBuilder();
			StringBuilder strSql2=new StringBuilder();
			if (model.sysNo != null)
			{
				strSql1.Append("sysNo,");
				strSql2.Append("'"+model.sysNo+"',");
			}
			if (model.insideNo != null)
			{
				strSql1.Append("insideNo,");
				strSql2.Append("'"+model.insideNo+"',");
			}
			if (model.businessDate != null)
			{
				strSql1.Append("businessDate,");
				strSql2.Append("'"+model.businessDate+"',");
			}
			if (model.deptNo != null)
			{
				strSql1.Append("deptNo,");
				strSql2.Append("'"+model.deptNo+"',");
			}
			if (model.applypsnNo != null)
			{
				strSql1.Append("applypsnNo,");
				strSql2.Append("'"+model.applypsnNo+"',");
			}
			if (model.accwayNo != null)
			{
				strSql1.Append("accwayNo,");
				strSql2.Append("'"+model.accwayNo+"',");
			}
			if (model.currencyNo != null)
			{
				strSql1.Append("currencyNo,");
				strSql2.Append("'"+model.currencyNo+"',");
			}
			if (model.rate != null)
			{
				strSql1.Append("rate,");
				strSql2.Append(""+model.rate+",");
			}
			if (model.loanNo != null)
			{
				strSql1.Append("loanNo,");
				strSql2.Append("'"+model.loanNo+"',");
			}
			if (model.loanMoney != null)
			{
				strSql1.Append("loanMoney,");
				strSql2.Append(""+model.loanMoney+",");
			}
			if (model.payMoney != null)
			{
				strSql1.Append("payMoney,");
				strSql2.Append(""+model.payMoney+",");
			}
			if (model.attachNum != null)
			{
				strSql1.Append("attachNum,");
				strSql2.Append(""+model.attachNum+",");
			}
            if (model.filldate != null)
            {
                strSql1.Append("fillpsn,");
                strSql2.Append("'" + model.fillpsn + "',");
            }
			if (model.filldate != null)
			{
				strSql1.Append("filldate,");
				strSql2.Append("'"+model.filldate+"',");
			}
			if (model.remarks != null)
			{
				strSql1.Append("remarks,");
				strSql2.Append("'"+model.remarks+"',");
			}
			if (model.auditFlag != null)
			{
				strSql1.Append("auditFlag,");
				strSql2.Append(""+model.auditFlag+",");
			}
			if (model.auditPsn != null)
			{
				strSql1.Append("auditPsn,");
				strSql2.Append("'"+model.auditPsn+"',");
			}
			if (model.auditDate != null)
			{
				strSql1.Append("auditDate,");
				strSql2.Append("'"+model.auditDate+"',");
			}
			if (model.authorFlag != null)
			{
				strSql1.Append("authorFlag,");
				strSql2.Append(""+model.authorFlag+",");
			}
			if (model.authorPsn != null)
			{
				strSql1.Append("authorPsn,");
				strSql2.Append("'"+model.authorPsn+"',");
			}
			if (model.authorDate != null)
			{
				strSql1.Append("authorDate,");
				strSql2.Append("'"+model.authorDate+"',");
			}
			if (model.approvFlag != null)
			{
				strSql1.Append("approvFlag,");
				strSql2.Append(""+model.approvFlag+",");
			}
			if (model.approvPsn != null)
			{
				strSql1.Append("approvPsn,");
				strSql2.Append("'"+model.approvPsn+"',");
			}
			if (model.approvDate != null)
			{
				strSql1.Append("approvDate,");
				strSql2.Append("'"+model.approvDate+"',");
			}
			if (model.cancelFlag != null)
			{
				strSql1.Append("cancelFlag,");
				strSql2.Append(""+model.cancelFlag+",");
			}
			if (model.cancelPsn != null)
			{
				strSql1.Append("cancelPsn,");
				strSql2.Append("'"+model.cancelPsn+"',");
			}
			if (model.cancelDate != null)
			{
				strSql1.Append("cancelDate,");
				strSql2.Append("'"+model.cancelDate+"',");
			}
			strSql.Append("insert into wcsy_costsOtherOrderMst(");
			strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
			strSql.Append(")");
			strSql.Append(" values (");
			strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
			strSql.Append(")");
			return strSql.ToString();
			
		}

		/// <summary>
		/// 更新一条数据
		/// </summary>
		public string Update(WCSY.MODEL.wcsy_costsOtherOrderMst model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update wcsy_costsOtherOrderMst set ");
			if (model.insideNo != null)
			{
				strSql.Append("insideNo='"+model.insideNo+"',");
			}
			else
			{
				strSql.Append("insideNo= null ,");
			}
			if (model.businessDate != null)
			{
				strSql.Append("businessDate='"+model.businessDate+"',");
			}
			else
			{
				strSql.Append("businessDate= null ,");
			}
			if (model.deptNo != null)
			{
				strSql.Append("deptNo='"+model.deptNo+"',");
			}
			else
			{
				strSql.Append("deptNo= null ,");
			}
			if (model.applypsnNo != null)
			{
				strSql.Append("applypsnNo='"+model.applypsnNo+"',");
			}
			else
			{
				strSql.Append("applypsnNo= null ,");
			}
			if (model.accwayNo != null)
			{
				strSql.Append("accwayNo='"+model.accwayNo+"',");
			}
			else
			{
				strSql.Append("accwayNo= null ,");
			}
			if (model.currencyNo != null)
			{
				strSql.Append("currencyNo='"+model.currencyNo+"',");
			}
			else
			{
				strSql.Append("currencyNo= null ,");
			}
			if (model.rate != null)
			{
				strSql.Append("rate="+model.rate+",");
			}
			else
			{
				strSql.Append("rate= null ,");
			}
			if (model.loanNo != null)
			{
				strSql.Append("loanNo='"+model.loanNo+"',");
			}
			else
			{
				strSql.Append("loanNo= null ,");
			}
			if (model.loanMoney != null)
			{
				strSql.Append("loanMoney="+model.loanMoney+",");
			}
			else
			{
				strSql.Append("loanMoney= null ,");
			}
			if (model.payMoney != null)
			{
				strSql.Append("payMoney="+model.payMoney+",");
			}
			else
			{
				strSql.Append("payMoney= null ,");
			}
			if (model.attachNum != null)
			{
				strSql.Append("attachNum="+model.attachNum+",");
			}
			else
			{
				strSql.Append("attachNum= null ,");
			}
            if (model.fillpsn != null)
            {
                strSql.Append("fillpsn='" + model.fillpsn + "',");
            }
            else
            {
                strSql.Append("fillpsn= null ,");
            }
			if (model.filldate != null)
			{
				strSql.Append("filldate='"+model.filldate+"',");
			}
			else
			{
				strSql.Append("filldate= null ,");
			}
			if (model.remarks != null)
			{
				strSql.Append("remarks='"+model.remarks+"',");
			}
			else
			{
				strSql.Append("remarks= null ,");
			}
			if (model.auditFlag != null)
			{
				strSql.Append("auditFlag="+model.auditFlag+",");
			}
			else
			{
				strSql.Append("auditFlag= null ,");
			}
			if (model.auditPsn != null)
			{
				strSql.Append("auditPsn='"+model.auditPsn+"',");
			}
			else
			{
				strSql.Append("auditPsn= null ,");
			}
			if (model.auditDate != null)
			{
				strSql.Append("auditDate='"+model.auditDate+"',");
			}
			else
			{
				strSql.Append("auditDate= null ,");
			}
			if (model.authorFlag != null)
			{
				strSql.Append("authorFlag="+model.authorFlag+",");
			}
			else
			{
				strSql.Append("authorFlag= null ,");
			}
			if (model.authorPsn != null)
			{
				strSql.Append("authorPsn='"+model.authorPsn+"',");
			}
			else
			{
				strSql.Append("authorPsn= null ,");
			}
			if (model.authorDate != null)
			{
				strSql.Append("authorDate='"+model.authorDate+"',");
			}
			else
			{
				strSql.Append("authorDate= null ,");
			}
			if (model.approvFlag != null)
			{
				strSql.Append("approvFlag="+model.approvFlag+",");
			}
			else
			{
				strSql.Append("approvFlag= null ,");
			}
			if (model.approvPsn != null)
			{
				strSql.Append("approvPsn='"+model.approvPsn+"',");
			}
			else
			{
				strSql.Append("approvPsn= null ,");
			}
			if (model.approvDate != null)
			{
				strSql.Append("approvDate='"+model.approvDate+"',");
			}
			else
			{
				strSql.Append("approvDate= null ,");
			}
			if (model.cancelFlag != null)
			{
				strSql.Append("cancelFlag="+model.cancelFlag+",");
			}
			else
			{
				strSql.Append("cancelFlag= null ,");
			}
			if (model.cancelPsn != null)
			{
				strSql.Append("cancelPsn='"+model.cancelPsn+"',");
			}
			else
			{
				strSql.Append("cancelPsn= null ,");
			}
			if (model.cancelDate != null)
			{
				strSql.Append("cancelDate='"+model.cancelDate+"',");
			}
			else
			{
				strSql.Append("cancelDate= null ,");
			}
			int n = strSql.ToString().LastIndexOf(",");
			strSql.Remove(n, 1);
			strSql.Append(" where sysNo='"+ model.sysNo+"' ");
			return strSql.ToString();
			
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(string sysNo)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from wcsy_costsOtherOrderMst ");
			strSql.Append(" where sysNo='"+sysNo+"' " );
			int rowsAffected=DBHelper.ExecuteSql(strSql.ToString());
			if (rowsAffected > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string sysNolist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from wcsy_costsOtherOrderMst ");
			strSql.Append(" where sysNo in ("+sysNolist + ")  ");
			int rows=DBHelper.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public WCSY.MODEL.wcsy_costsOtherOrderMst GetModel(string sysNo)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1  ");
			strSql.Append(" sysNo,insideNo,businessDate,deptNo,applypsnNo,accwayNo,currencyNo,rate,loanNo,loanMoney,payMoney,attachNum,fillpsn,filldate,remarks,auditFlag,auditPsn,auditDate,authorFlag,authorPsn,authorDate,approvFlag,approvPsn,approvDate,cancelFlag,cancelPsn,cancelDate ");
			strSql.Append(" from wcsy_costsOtherOrderMst ");
			strSql.Append(" where sysNo='"+sysNo+"' " );
			WCSY.MODEL.wcsy_costsOtherOrderMst model=new WCSY.MODEL.wcsy_costsOtherOrderMst();
			DataSet ds=DBHelper.Query(strSql.ToString());
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["sysNo"]!=null && ds.Tables[0].Rows[0]["sysNo"].ToString()!="")
				{
					model.sysNo=ds.Tables[0].Rows[0]["sysNo"].ToString();
				}
				if(ds.Tables[0].Rows[0]["insideNo"]!=null && ds.Tables[0].Rows[0]["insideNo"].ToString()!="")
				{
					model.insideNo=ds.Tables[0].Rows[0]["insideNo"].ToString();
				}
				if(ds.Tables[0].Rows[0]["businessDate"]!=null && ds.Tables[0].Rows[0]["businessDate"].ToString()!="")
				{
					model.businessDate=DateTime.Parse(ds.Tables[0].Rows[0]["businessDate"].ToString());
				}
				if(ds.Tables[0].Rows[0]["deptNo"]!=null && ds.Tables[0].Rows[0]["deptNo"].ToString()!="")
				{
					model.deptNo=ds.Tables[0].Rows[0]["deptNo"].ToString();
				}
				if(ds.Tables[0].Rows[0]["applypsnNo"]!=null && ds.Tables[0].Rows[0]["applypsnNo"].ToString()!="")
				{
					model.applypsnNo=ds.Tables[0].Rows[0]["applypsnNo"].ToString();
				}
				if(ds.Tables[0].Rows[0]["accwayNo"]!=null && ds.Tables[0].Rows[0]["accwayNo"].ToString()!="")
				{
					model.accwayNo=ds.Tables[0].Rows[0]["accwayNo"].ToString();
				}
				if(ds.Tables[0].Rows[0]["currencyNo"]!=null && ds.Tables[0].Rows[0]["currencyNo"].ToString()!="")
				{
					model.currencyNo=ds.Tables[0].Rows[0]["currencyNo"].ToString();
				}
				if(ds.Tables[0].Rows[0]["rate"]!=null && ds.Tables[0].Rows[0]["rate"].ToString()!="")
				{
					model.rate=decimal.Parse(ds.Tables[0].Rows[0]["rate"].ToString());
				}
				if(ds.Tables[0].Rows[0]["loanNo"]!=null && ds.Tables[0].Rows[0]["loanNo"].ToString()!="")
				{
					model.loanNo=ds.Tables[0].Rows[0]["loanNo"].ToString();
				}
				if(ds.Tables[0].Rows[0]["loanMoney"]!=null && ds.Tables[0].Rows[0]["loanMoney"].ToString()!="")
				{
					model.loanMoney=decimal.Parse(ds.Tables[0].Rows[0]["loanMoney"].ToString());
				}
				if(ds.Tables[0].Rows[0]["payMoney"]!=null && ds.Tables[0].Rows[0]["payMoney"].ToString()!="")
				{
					model.payMoney=decimal.Parse(ds.Tables[0].Rows[0]["payMoney"].ToString());
				}
				if(ds.Tables[0].Rows[0]["attachNum"]!=null && ds.Tables[0].Rows[0]["attachNum"].ToString()!="")
				{
					model.attachNum=int.Parse(ds.Tables[0].Rows[0]["attachNum"].ToString());
				}
                if (ds.Tables[0].Rows[0]["fillpsn"] != null && ds.Tables[0].Rows[0]["fillpsn"].ToString() != "")
                {
                    model.fillpsn = ds.Tables[0].Rows[0]["fillpsn"].ToString();
                }
				if(ds.Tables[0].Rows[0]["filldate"]!=null && ds.Tables[0].Rows[0]["filldate"].ToString()!="")
				{
					model.filldate=DateTime.Parse(ds.Tables[0].Rows[0]["filldate"].ToString());
				}
				if(ds.Tables[0].Rows[0]["remarks"]!=null && ds.Tables[0].Rows[0]["remarks"].ToString()!="")
				{
					model.remarks=ds.Tables[0].Rows[0]["remarks"].ToString();
				}
				if(ds.Tables[0].Rows[0]["auditFlag"]!=null && ds.Tables[0].Rows[0]["auditFlag"].ToString()!="")
				{
					model.auditFlag=int.Parse(ds.Tables[0].Rows[0]["auditFlag"].ToString());
				}
				if(ds.Tables[0].Rows[0]["auditPsn"]!=null && ds.Tables[0].Rows[0]["auditPsn"].ToString()!="")
				{
					model.auditPsn=ds.Tables[0].Rows[0]["auditPsn"].ToString();
				}
				if(ds.Tables[0].Rows[0]["auditDate"]!=null && ds.Tables[0].Rows[0]["auditDate"].ToString()!="")
				{
					model.auditDate=DateTime.Parse(ds.Tables[0].Rows[0]["auditDate"].ToString());
				}
				if(ds.Tables[0].Rows[0]["authorFlag"]!=null && ds.Tables[0].Rows[0]["authorFlag"].ToString()!="")
				{
					model.authorFlag=int.Parse(ds.Tables[0].Rows[0]["authorFlag"].ToString());
				}
				if(ds.Tables[0].Rows[0]["authorPsn"]!=null && ds.Tables[0].Rows[0]["authorPsn"].ToString()!="")
				{
					model.authorPsn=ds.Tables[0].Rows[0]["authorPsn"].ToString();
				}
				if(ds.Tables[0].Rows[0]["authorDate"]!=null && ds.Tables[0].Rows[0]["authorDate"].ToString()!="")
				{
					model.authorDate=DateTime.Parse(ds.Tables[0].Rows[0]["authorDate"].ToString());
				}
				if(ds.Tables[0].Rows[0]["approvFlag"]!=null && ds.Tables[0].Rows[0]["approvFlag"].ToString()!="")
				{
					model.approvFlag=int.Parse(ds.Tables[0].Rows[0]["approvFlag"].ToString());
				}
				if(ds.Tables[0].Rows[0]["approvPsn"]!=null && ds.Tables[0].Rows[0]["approvPsn"].ToString()!="")
				{
					model.approvPsn=ds.Tables[0].Rows[0]["approvPsn"].ToString();
				}
				if(ds.Tables[0].Rows[0]["approvDate"]!=null && ds.Tables[0].Rows[0]["approvDate"].ToString()!="")
				{
					model.approvDate=DateTime.Parse(ds.Tables[0].Rows[0]["approvDate"].ToString());
				}
				if(ds.Tables[0].Rows[0]["cancelFlag"]!=null && ds.Tables[0].Rows[0]["cancelFlag"].ToString()!="")
				{
					model.cancelFlag=int.Parse(ds.Tables[0].Rows[0]["cancelFlag"].ToString());
				}
				if(ds.Tables[0].Rows[0]["cancelPsn"]!=null && ds.Tables[0].Rows[0]["cancelPsn"].ToString()!="")
				{
					model.cancelPsn=ds.Tables[0].Rows[0]["cancelPsn"].ToString();
				}
				if(ds.Tables[0].Rows[0]["cancelDate"]!=null && ds.Tables[0].Rows[0]["cancelDate"].ToString()!="")
				{
					model.cancelDate=DateTime.Parse(ds.Tables[0].Rows[0]["cancelDate"].ToString());
				}
				return model;
			}
			else
			{
				return null;
			}
		}
		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select sysNo,insideNo,businessDate,deptNo,applypsnNo,accwayNo,currencyNo,rate,loanNo,loanMoney,payMoney,attachNum,filldate,remarks,auditFlag,auditPsn,auditDate,authorFlag,authorPsn,authorDate,approvFlag,approvPsn,approvDate,cancelFlag,cancelPsn,cancelDate ");
			strSql.Append(" FROM wcsy_costsOtherOrderMst ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return DBHelper.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" sysNo,insideNo,businessDate,deptNo,applypsnNo,accwayNo,currencyNo,rate,loanNo,loanMoney,payMoney,attachNum,fillpsn,filldate,remarks,auditFlag,auditPsn,auditDate,authorFlag,authorPsn,authorDate,approvFlag,approvPsn,approvDate,cancelFlag,cancelPsn,cancelDate ");
			strSql.Append(" FROM wcsy_costsOtherOrderMst ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DBHelper.Query(strSql.ToString());
		}

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public int GetRecordCount(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) FROM wcsy_costsOtherOrderMst ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			object obj = DBHelper.GetSingle(strSql.ToString());
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby );
			}
			else
			{
				strSql.Append("order by T.sysNo desc");
			}
			strSql.Append(")AS Row, T.*  from wcsy_costsOtherOrderMst T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DBHelper.Query(strSql.ToString());
		}

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetAllListByPara(string sysno, string dept, string applypsn, string fillpsn, string filldateB, string filldateE)
        {
            string sql = @"
select mst.*,deptinfo.*,fg_empinfo.empname,det.locsum from wcsy_costsotherordermst as mst
left join fg_deptinfo as deptinfo on mst.deptno=deptinfo.id
left join fg_empinfo on mst.applypsnno = fg_empinfo.id
left join (select sysno,sum(isnull(applymoney,0)) as locsum from wcsy_costsotherorderdet group by sysno) as det on mst.sysno = det.sysno";
            sql += " where 1=1 ";
            if (!sysno.Equals(""))
            {
                sql += " and mst.sysno like '%" + sysno + "%'";
            }
            if (!dept.Equals("0"))
            {
                sql += " and mst.deptno = '" + dept + "'";
            }
            if (!applypsn.Equals(""))
            {
                sql += " and fg_empinfo.empname like '%" + applypsn + "%'";
            }
            if (!fillpsn.Equals(""))
            {
                sql += " and mst.fillpsn like '%" + fillpsn + "%'";
            }
            if (!filldateB.Equals(""))
            {
                sql += " and mst.filldate>='" + filldateB + "'";
            }
            if (!filldateE.Equals(""))
            {
                sql += " and mst.filldate<='" + filldateE + "' ";
            }
            return DBHelper.Query(sql);
        }
		/*
		*/

		#endregion  Method
    }
}
